//201902104003邵潇漩
package bysj.dao;

import bysj.domain.StudentClass;
import bysj.service.DepartmentService;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class StudentClassDao {
    private static StudentClassDao studentClassDao = null;
    private StudentClassDao() {
    }

    public static StudentClassDao getInstance() {
        if (studentClassDao == null){
            studentClassDao = new StudentClassDao();
        }
        return studentClassDao;
    }
    public StudentClass find(Connection conn, Integer id) throws SQLException {
        StudentClass desiredStudentClass = null;
        String sql_select_id="SELECT*FROM StudentClass WHERE id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql_select_id);
        pstmt.setInt(1,id);
        ResultSet resultSet=pstmt.executeQuery();
        if(resultSet.next()){
            String name=resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int departmentId=resultSet.getInt("department_id");
            desiredStudentClass=new StudentClass(id,name,no,remarks, DepartmentService.getInstance().find(departmentId));
        }
        util.JdbcHelper.close(resultSet,pstmt);
        return desiredStudentClass;
    }
    public static Collection<StudentClass> findAll(Connection connection , List<util.Condition> conditionList, util.Pagination pagination) throws SQLException {
        Collection<StudentClass> studentClasses = new TreeSet<StudentClass>();
        int totalNum = CommonDao.getInstance().count(connection,"studentClass");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM studentClass");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = util.Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }

        //获得查询的预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //遍历结果集，根据元组创建对象，并添加到集合中
        while(resultSet.next()){
            studentClasses.add(new StudentClass(resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks"),
                    bysj.service.DepartmentService.getInstance().find(resultSet.getInt("department_id"))));
        }
        util.JdbcHelper.close(resultSet,pstmt);
        return studentClasses;
    }
}
